package com.bj58.groupbuy.action.service.impl;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import com.bj58.groupbuy.action.db.CommonDao;
import com.bj58.groupbuy.action.model.WanChannelDay;
import com.bj58.groupbuy.action.model.WanCompany;
import com.bj58.groupbuy.action.model.WanGame;
import com.bj58.groupbuy.action.model.WanOrderStat;
import com.bj58.groupbuy.action.model.WanService;
import com.bj58.groupbuy.action.service.WebGameService;
import com.bj58.groupbuy.wf.page.Paginate;

public class WebGameServiceImpl implements WebGameService {
	
	
	private CommonDao comDao = new CommonDao();
	@Override
	public List<WanCompany> getCompanyList() throws Exception {
		String sql = "select * from wan_company";
		return  comDao.getAllColsByVo(sql, WanCompany.class, 11);
	}

	@Override
	public List<WanGame> getGameList(int companyId) throws Exception {
		String sql = "select * from wan_game where company_id="+companyId;
		List<String> cols = new ArrayList<String>();
		cols.add("game_id");cols.add("company_id");cols.add("game_name");
		return comDao.getCommonStatList(sql, cols, WanGame.class, 11);
	}

	
	@Override
	public Paginate<WanOrderStat> wanOrderStat(List<String> cols,String condition,
			String orderby, int pageNum, int pageIndex, int type,int all)//all表示是否汇总
			throws Exception {
		//计算总记录数
		String countSql = getSqlForWGOS(1,type);
		countSql = countSql.replace("*where*", condition);
		countSql = countSql.replace("*select*", "stat_date");
		int count = comDao.countAll(countSql, 11);
		//分页查询
		String searchSql = null;
		if(all==1){
			searchSql = getSqlForWGOS(2,type);
		}else{
			searchSql = getSqlForWGOS(0,type);
		}
		searchSql = searchSql.replace("*where*", condition);
		searchSql = searchSql+" "+orderby;
		
		StringBuffer select = new StringBuffer();
		for(String col : cols){
			if("stat_date".equals(col) || "hour".equals(col)){
				select.append(col);
			}else if("arpu".equals(col)){
				select.append("sum(pay_amount)/sum(pay_user_count) as  arpu");
			}else{
				select.append("sum("+col+") as "+col);
			}
			select.append(",");
		}
		select.delete(select.length()-1,select.length());
		searchSql = searchSql.replace("*select*", select.toString());
		if(pageIndex >= 0 && all == 0){
			String limit ="limit "+(pageIndex-1)*pageNum+","+pageNum;//limit分页
			searchSql = searchSql+ " "+limit;
		}
		List<WanOrderStat> rsList = comDao.getCommonStatList(searchSql, cols,WanOrderStat.class,11);
		Paginate<WanOrderStat> pList = new Paginate<WanOrderStat>(count,pageIndex,pageNum,rsList);
		return pList;
	}
	
	
	private String getSqlForWGOS(int isCount,int type){
		StringBuffer rawSql = new StringBuffer();
		if(isCount == 1){
			rawSql.append("select  count(1) from (  ");
		}
		
		rawSql.append("select  *select*  ");
		if(type == 0 ){
			rawSql.append(" from wan_order_day ");
		}else if(type == 1){
			rawSql.append(" from wan_order_hour  ");
		}
		rawSql.append(" where 1=1 *where* ");
		if(isCount!=2){
			if(type == 0 ){
				rawSql.append(" group by  stat_date ");
			}else if(type == 1){
				rawSql.append(" group by  hour ");
			}
		}else{
			rawSql.append(" ");
		}
		
		if(isCount == 1){
			rawSql.append(") s ");
		}
		return rawSql.toString();
	}

	@Override
	public List<WanService> getServiceList(long companyId,long gameId){
		List<WanService> listService = new LinkedList<WanService>();
		String sql="select DISTINCT server_id,server_name,game_id  from wan_channel_day ";
		String sqlCondition=" where  game_id= "+gameId+" and company_id="+companyId;
		sql=sql+sqlCondition;
	
		List<String> cols = new ArrayList<String>();
		cols.add("server_id");
		cols.add("server_name");
		cols.add("game_id");
		try{
			listService = comDao.getCommonStatList(sql, cols, WanService.class, 11);
		}catch(Exception e){
			e.printStackTrace();
		}
		return listService;
	}
	
	@Override
	public Paginate<WanChannelDay> wanChannelDayStat(List<String> cols,String condition,
			String orderby, int pageNum, int pageIndex, int type,int all)
			throws Exception {
		//计算总记录数
		String countSql = getSqlForWCDS(1,type);
		countSql = countSql.replace("*where*", condition);
		if(type==0)
			countSql = countSql.replace("*select*", "channel_name");
		else if(type==1)
			countSql = countSql.replace("*select*", "game_name");
		else if(type==2)
			countSql = countSql.replace("*select*", "server_name");
		
		int count = comDao.countAll(countSql, 11);
		//分页查询
		String searchSql = getSqlForWCDS(0,type);
		if(all==1){
			searchSql = searchSql.substring(0,searchSql.indexOf("group by"));
		}
		searchSql = searchSql.replace("*where*", condition);
		searchSql = searchSql+" "+orderby;
		
		StringBuffer select = new StringBuffer();
		for(String col : cols){
			if( "channel_name".equals(col) || "game_name".equals(col) || "server_name".equals(col) ){
				select.append(col);
			}else if("arpu".equals(col)){
				select.append("sum(pay_amount)/sum(pay_user_count) as  arpu");
			}else{
				select.append("sum("+col+") as "+col);
			}
			select.append(",");
		}
		
		
		select.delete(select.length()-1,select.length());

		searchSql = searchSql.replace("*select*", select.toString());
		if(pageIndex >= 0){
			String limit ="limit "+(pageIndex-1)*pageNum+","+pageNum;//limit分页
			searchSql = searchSql+ " "+limit;
		}
		
		
		List<WanChannelDay> rsList = comDao.getCommonStatList(searchSql, cols,WanChannelDay.class,11);
		Paginate<WanChannelDay> pList = new Paginate<WanChannelDay>(count,pageIndex,pageNum,rsList);
		return pList;
	}
	
	//按维度和时间查询
	public Paginate<WanChannelDay>  wanChannelDayStat(List<String> cols,String condition,String orderby,int pageNum,int pageIndex,int type,int all,String groupby)throws Exception{
		//计算总记录数
		String countSql = getSqlForWCDS2(1,type);
		countSql = countSql.replace("*where*", condition);
		if(type==0){
			countSql = countSql.replace("*select*", "channel_name");
		}
		else if(type==1){
			countSql = countSql.replace("*select*", "game_name");
		}	
		else if(type==2){
			countSql = countSql.replace("*select*", "server_name");
		}
			
		
		int count = comDao.countAll(countSql, 11);
		//分页查询
		
		String searchSql=null;
		if(all==1){
			searchSql= getSqlForWCDS2(2,type);
		}else{
			searchSql= getSqlForWCDS2(0,type);
		}
		searchSql = searchSql.replace("*where*", condition);
		searchSql = searchSql+" "+orderby;
		
		StringBuffer select = new StringBuffer();
		
		for(String col : cols){
			if( "stat_date".equals(col) ||"channel_name".equals(col) || "game_name".equals(col) || "server_name".equals(col) ){
				select.append(col);
			}else if("arpu".equals(col)){
				select.append("sum(pay_amount)/sum(pay_user_count) as  arpu");
			}else{
				select.append("sum("+col+") as "+col);
			}
			select.append(",");
		}
		
		
		select.delete(select.length()-1,select.length());

		searchSql = searchSql.replace("*select*", select.toString());
		if(pageIndex >= 0){
			String limit ="limit "+(pageIndex-1)*pageNum+","+pageNum;//limit分页
			searchSql = searchSql+ " "+limit;
		}
		
		List<WanChannelDay> rsList = comDao.getCommonStatList(searchSql, cols,WanChannelDay.class,11);
		Paginate<WanChannelDay> pList = new Paginate<WanChannelDay>(count,pageIndex,pageNum,rsList);
		return pList;
	}

	
	private String getSqlForWCDS(int isCount,int type){
		StringBuffer rawSql = new StringBuffer();
		if(isCount == 1){
			rawSql.append("select  count(1) from (  ");
		}
		
		rawSql.append("select  *select*  ");
	
		rawSql.append(" from wan_channel_day ");
		
		rawSql.append(" where 1=1 *where* ");
		if(type==0)
		{
			rawSql.append(" group by channel_name ");
		}
		else if(type==1)
		{
			rawSql.append(" group by game_name ");
		}
		else if(type==2)
		{
			rawSql.append(" group by server_name ");
		}
		
		
		if(isCount == 1){
			rawSql.append(") s ");
		}
		return rawSql.toString();
	}




private String getSqlForWCDS2(int isCount,int type){
	StringBuffer rawSql = new StringBuffer();
	if(isCount == 1){
		rawSql.append("select  count(1) from (  ");
	}
	
	rawSql.append("select  *select*  ");

	rawSql.append(" from wan_channel_day ");
	
	rawSql.append(" where 1=1 *where* ");
	if(isCount==2){
		rawSql.append(" group by  stat_date");
	}else{
		if(type==0)
		{
			rawSql.append(" group by  stat_date,channel_name ");
		}
		else if(type==1)
		{
			rawSql.append(" group by  stat_date,game_name ");
		}
		else if(type==2)
		{
			rawSql.append(" group by  stat_date,server_name ");
		}
	}
	
	if(isCount == 1){
		rawSql.append(") s ");
	}
	return rawSql.toString();
}
}